using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.Data.SqlServerCe;
using System.Reflection;
using NUnit.Framework;
using ODX.Core;
using ODX.LazyUnitTester;

namespace DbTests
{
    [Table("Human")]
    //[UniqueConstraint("Name")]
    public abstract class Human : Entity
    {
        //[Column(Unique = true)]
        public abstract string Name { get; set; }
        public abstract DateTime BirthDate { get; set; }
        public int Age
        {
            get { return (int)((DateTime.Now - BirthDate).Days / 365.25); }
        }
    }

    [TestFixture]
    public class C99_DbTests
    {
        [TestBody]
        static void Main()
        {
            Test(OracleClientFactory.Instance, "User Id=odx;Password=odx", NullsPosition.Maximum);
            Test(SqlCeProviderFactory.Instance, "Data Source=c:\\sqlce.sdf;Password=odx", NullsPosition.Minimum);
            Test(OdbcFactory.Instance, "Driver={PostgreSQL ANSI};Server=localhost;Database=ODX;Uid=odx;Pwd=odx;", NullsPosition.Maximum);
            TestInsertUpadte();

        }
        static void Test(DbProviderFactory factory, string connStr, NullsPosition nullsPosition)
        {
            try
            {
                LUT.WriteLine(factory);
                DbDataProvider provider = new DbDataProvider(factory, connStr);
                Session s = new Session(provider);
                s.RegisterAssembly(Assembly.GetExecutingAssembly());
                s.Prepare();

                foreach (Human human in s.All<Human>()) human.Delete();
                s.Save();

                provider.NullsPosition = nullsPosition;

                int N = 100;
                for (int i = 0; i < N; i++)
                {
                    Human h = s.Create<Human>();
                    if (i % 10 != 0)
                    {
                        h.BirthDate = new DateTime(1977, 04, 06) + TimeSpan.FromDays(i*100);
                        h.Name = (h.BirthDate.Month * (i % 10)).ToString();
                    }
                    else
                        h.Name = (i/10).ToString();
                }

                s.Save(SaveMode.Overwrite);


                Session pagerSession = s.Clone();

                DataSet selection = provider.SelectSql(@"
select h.ID, h.Name, h.BirthDate, count(*) as cnt from Human h inner join Human th on h.Name = th.Name
group by h.ID, h.Name, h.BirthDate
having count(*) = 3
order by cnt desc, h.Name
", "Human", 0, null);

                
                foreach ( Human h in pagerSession.Merge<Human>(selection))
                    LUT.WriteLine(h.Name);

                LUT.WriteLine("----------------------------------------------------");

                Pager<Human> humanPager = new Pager<Human>(pagerSession, "select * from Human where <PagerWhere>", "BirthDate desc, Name", 5, PagerFilterType.Select);

                foreach (Human h in humanPager)
                    LUT.WriteLine("{0} : {1}", h.BirthDate, h.Name);
            }
            catch(Exception e)
            {
                LUT.WriteLine(e);
            }
        }

        static void TestInsertUpadte()
        {
            DbDataProvider provider = new DbDataProvider(OracleClientFactory.Instance, "User Id=odx;Password=odx");
            Session s1 = CreateSession(provider);
            Session s2 = CreateSession(provider);
            Session s3 = CreateSession(provider);

            foreach (Human human in s3.All<Human>()) human.Delete();
            s3.Save();

            Human john1 = s1.Create<Human>();
            john1.Name = "John 1";

            s1.Save();

            Human john2 = (Human) s2.Merge(john1);

            LUT.WriteLine(john2.IsNew);
            LUT.WriteLine(john2.IsChanged);

            john1.Delete();
            s1.Save();

            s2.Save(SaveMode.Complete);
        }

        private static Session CreateSession(DbDataProvider provider)
        {
            Session s = new Session(provider);
            s.RegisterAssembly(Assembly.GetExecutingAssembly());
            s.Prepare();
            return s;
        }

        [Test]
        public void Test()
        {
            //LUT.Execute(Main);
        }
    }
}
